// Created by Xslt generator for Eclipse.
// XSL :  not found (java.io.FileNotFoundException:  (The system cannot find the path specified))
// Default XSL used : easystruts.jar$org.easystruts.xslgen.JavaClass.xsl

package com.ba360.struts.action;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.ba360.portal.common.PortalConstants;
import com.ba360.portal.database.DBConnectionImpl;
import com.ba360.portal.database.dbo.Classifieds;
import com.ba360.portal.util.Log;
import com.ba360.struts.form.SearchClassifiedsForm;
import com.ba360.struts.form.SearchClassifiedsForm_23Feb09;

/**
 * SearchClassifiedsAction.java created by EasyStruts - XsltGen.
 * http://easystruts.sf.net created on 05-06-2004
 * 
 * XDoclet definition:
 * 
 * @struts:action
 */
public class SearchClassifiedsAction_23Feb09 extends Action {

	// --------------------------------------------------------- Instance
	// Variables

	// --------------------------------------------------------- Methods

	/**
	 * Method execute
	 * 
	 * @param ActionMapping
	 *            mapping
	 * @param ActionForm
	 *            form
	 * @param HttpServletRequest
	 *            request
	 * @param HttpServletResponse
	 *            response
	 * @return ActionForward
	 * @throws Exception
	 */
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {

		// throw new UnsupportedOperationException("Generated method
		// 'execute(...)' not implemented.");
		Connection conn = null;
		Statement stmt = null;

		try {
			Log.init(this.getClass());
			Log.info("SearchClassifiedsAction");
			String action = request.getParameter(PortalConstants.ACTION);
			if (action != null) {
				if (action.equals(PortalConstants.SEARCH_AD)) {

					DBConnectionImpl dbConn = new DBConnectionImpl();
					conn = dbConn.getConnection();
					stmt = conn.createStatement();
					adInfo(conn, stmt, form, request);
					stmt.close();
					// conn.close();
				}
			}
		} catch (Exception e) {
			Log.error("Error in searching ad :" + e.getMessage());
			throw e;
		} finally {
			// stmt.close();
			conn.close();
		}
		return mapping.findForward(PortalConstants.PAGE_SUCCESS);
	}

	private void adInfo(Connection conn, Statement stmt, ActionForm searchForm,
			HttpServletRequest request) throws Exception {
		ResultSet rs = null;
		Statement stmt1 = null;

		try {
			// SearchClassifiedsForm searchAd = (SearchClassifiedsForm) searchForm; Modified by Arun on 09-MAY-2009
			SearchClassifiedsForm_23Feb09 searchAd = (SearchClassifiedsForm_23Feb09) searchForm;
			
			String mainCatType = searchAd.getParentCategoryId();
			String subCatType = searchAd.getSubCategoryID();
			String cityId = searchAd.getCityId();
			// String adContent = searchAd.getAdContent();
			String priceMin = searchAd.getPriceMin();
			String priceMax = searchAd.getPriceMax();
			String content = searchAd.getAdContent();
			String excludeSearch = searchAd.getExcludeSearchContent();
			String listPict = searchAd.getListPict();

			if (excludeSearch != null) {
				if (excludeSearch.equals("")) {
					excludeSearch = null;
				}
			}
			if (content != null) {
				if (content.equals("") || content == null) {
					content = null;
				}
			}
			Log.info("Ad Content Search: " + content + "  " + excludeSearch);
			String whereClause = "where ";

			// Totally delete advs which is over 100 days old.
			// Reverted back on 13.06.2007
			// deleteOldAdvs(stmt);

			if (priceMin != null) {
				if (priceMin.length() != 0 && !priceMin.equals("0")) {

					// int pricemin = Integer.parseInt(priceMin);

					whereClause = whereClause + "price >=" + priceMin;

				} /*
					 * else { whereClause = whereClause;// + "price >0 "; }
					 */
			}
			Log.info("Ad Content Search: 1");
			if (priceMax != null && priceMax.length() != 0) {
				if (priceMin == null || priceMin.length() == 0
						|| priceMin.equals("0")) {
					whereClause = whereClause + "price >0 and price <="
							+ priceMax;
				} else {
					if (priceMax.length() != 0) {

						whereClause = whereClause + " and price <=" + priceMax;
					}
				}/*
					 * else { whereClause = whereClause;// + " and price <
					 * select max(price) from ads "; }
					 */
			}
			Log.info("Ad Content Search: 2");
			if (cityId != null) {
				if (!cityId.equals("0")) {
					if (!whereClause.trim().equalsIgnoreCase("where")) {

						whereClause = whereClause + " and city_id="
								+ Integer.parseInt(cityId);
					} else {
						whereClause = whereClause + " city_id="
								+ Integer.parseInt(cityId);
					}

				}
			}
			Log.info("Ad Content Search: subCatType" + subCatType);
			if (subCatType != null) {
				if (!subCatType.equals("0")) {
					if (!whereClause.trim().equalsIgnoreCase("where")) {

						whereClause = whereClause + " and category_id="
								+ Integer.parseInt(subCatType);
					} else {
						whereClause = whereClause + " category_id="
								+ Integer.parseInt(subCatType);
					}

				}

			}
			Log.info("Ad Content Search: 4");
			/*
			 * if (subCatType == null || subCatType.equals("0")) { whereClause =
			 * whereClause + " category_id in (select category_id from
			 * categories where par_category_id="+
			 * Integer.parseInt(mainCatType)+")"; }
			 */

			if (content != null && excludeSearch != null) {
				if (!content.equals("") && !excludeSearch.equals("")) {
					if (!whereClause.trim().equalsIgnoreCase("where")) {

						whereClause = whereClause + " and (name like '%"
								+ content + "%' or ad_description like '%"
								+ content + "%') and (name not like '%"
								+ excludeSearch
								+ "%' and ad_description not like '%"
								+ excludeSearch + "%')";
					} else {
						whereClause = whereClause + " (name like '%" + content
								+ "%' or ad_description like '%" + content
								+ "%') and (name not like '%" + excludeSearch
								+ "%' and ad_description not like '%"
								+ excludeSearch + "%')";
					}
				}
			} else {

				if (content != null) {
					if (!content.equals("")) {
						if (!whereClause.trim().equalsIgnoreCase("where")) {

							whereClause = whereClause + " and (name like '%"
									+ content + "%' or ad_description like '%"
									+ content + "%')";
						} else {
							whereClause = whereClause + " (name like '%"
									+ content + "%' or ad_description like '%"
									+ content + "%')";
						}

					}
				}
				Log.info("Ad Content Search: 5");

				if (excludeSearch != null) {
					if (!excludeSearch.equals("")) {

						if (!whereClause.trim().equalsIgnoreCase("where")) {

							whereClause = whereClause
									+ " and (name not like '%" + excludeSearch
									+ "%' and ad_description not like '%"
									+ excludeSearch + "%')";
						} else {
							whereClause = whereClause + " name not like '%"
									+ excludeSearch
									+ "%' and ad_description not like '%"
									+ excludeSearch + "%'";
						}

					}
				}
			}
			// Search all ads where mainCatID is request value
			/*
			 * if (mainCatType != null && !mainCatType.equals("99")) { if
			 * (!whereClause.trim().equalsIgnoreCase("where")) {
			 * 
			 * whereClause = whereClause + " and } else { whereClause =
			 * whereClause + " name not like '%" + excludeSearch + "%' and
			 * ad_description not like '%" + excludeSearch + "%'"; } }
			 */

			// Code for restricting the result set to get the latest 250 days
			// ads.
			// Get the current date.
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Calendar today = Calendar.getInstance();
			// move to 100 days back
			today.add(Calendar.DAY_OF_YEAR, -100);

			if (!whereClause.trim().equalsIgnoreCase("where")) {

				whereClause = whereClause + " and date_posted > '"
						+ sdf.format(today.getTime()) + "'";
			} else {
				whereClause = whereClause + " date_posted > '"
						+ sdf.format(today.getTime()) + "'";
			}

			if (whereClause.trim().equalsIgnoreCase("where")) {
				whereClause = "";
			}

			Log.info("Search Query :" + "select * from ads " + whereClause
					+ " order by date_posted desc");
			rs = stmt.executeQuery("select * from ads " + whereClause
					+ " order by date_posted desc");

			List myAdList = new ArrayList();
			HttpSession session = request.getSession();

			// Get the path to the /Users/images from the servlet context.
			ServletContext theApplicationsServletContext = (ServletContext) this
					.getServlet().getServletContext();
			String realPath;
			int userId;

			stmt1 = conn.createStatement();
			while (rs.next()) {
				Classifieds ads = new Classifieds();
				ads.setAdvId(rs.getInt(ads.ADV_ID));

				ads.setActiveDate(rs.getDate(ads.ADV_ACTIVEUNTIL));
				ads.setAdDesc(rs.getString(ads.ADV_TRADEDESC));

				ads.setAdTitle(rs.getString(ads.ADV_TITLE));

				userId = getUserId(ads.getAdvId(), stmt1);
				realPath = theApplicationsServletContext
						.getRealPath("\\droik\\Users\\images\\" + userId);
				File dir = new File(realPath + "/" + ads.getAdvId());

				if ("on".equals(listPict)) {
					if (dir.isDirectory()) {
						File[] dirs = dir.listFiles();
						if (dirs.length == 0) {
							continue;
						}
					} else {
						continue;
					}
				}

				if (dir.isDirectory()) {
					File[] dirs = dir.listFiles();
					if (dirs.length > 0) {
						ads.setAdTitle(ads.getAdTitle()
								+ " <font size=1 color=\"red\">Pic</font>");
					}
				}

				ads.setCityId(rs.getInt(ads.ADV_CITY));

				String city = dLookUp(stmt1, "lookup_city", "cityname",
						"city_id=" + ads.getCityId());

				ads.setCity(city);
				ads.setCatId(rs.getInt(ads.ADV_CATID));
				ads.setContactEmail(rs.getString(ads.ADV_CONTACTEMAIL));
				ads.setContactName(rs.getString(ads.ADV_CONTACTNAME));
				ads.setContactNumber(rs.getString(ads.ADV_CONTACTNUM));
				ads.setDatePosted(rs.getTimestamp(ads.ADV_DATE));
				ads.setHouseType(rs.getString(ads.ADV_HOUSETYPE));
				Log.info("City ID:" + ads.getCityId());
				Log.info("Category ID:" + ads.getCatId());

				int mainCatSearchId = 0;
				if (mainCatType != null && !mainCatType.equals("99")) {
					Log.info("Ad Content Search: CatId " + ads.getCatId());
					int parent_cat_id = Integer.parseInt(dLookUp(stmt1,
							"categories", "par_category_id", "category_id="
									+ ads.getCatId()));
					Log.info(ads.getCatId() + "  " + parent_cat_id);
					Log.info("Ad Content Search: 31");
					mainCatSearchId = Integer.parseInt(mainCatType);
					Log.info("Ad Content Search: 13");

					if (mainCatSearchId != parent_cat_id) {
						Log.info("Continuing - parent id does not match"
								+ mainCatSearchId + " " + parent_cat_id);
						continue;
					}

					String parentCatName = dLookUp(stmt1, "categories", "name",
							"category_id=" + parent_cat_id);
					ads.setParentCategoryName(parentCatName);
					ads.setParentCatId(parent_cat_id);
				}
				ads.setPrice(rs.getString(ads.ADV_PRICE));
				ads.setSqFt(rs.getString(ads.ADV_SQFT));
				String subCatName = dLookUp(stmt1, "categories", "name",
						"category_id=" + ads.getCatId());
				ads.setSubCategoryName(subCatName);
				myAdList.add(ads);
				Log.info("Adding:" + ads.getAdTitle());

			}

			session.setAttribute("SEARCHRESULT", myAdList);
		} catch (SQLException sqlEx) {
			Log.error(sqlEx.getMessage());
			request.getSession().setAttribute("SEARCHRESULT", new ArrayList());
		} finally {

			rs.close();
			stmt1.close();
			stmt.close();
		}

	}

	private void deleteOldAdvs(Statement stmt) {

		try {
			// Get the current date.
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			Calendar today = Calendar.getInstance();
			// move to 100 days back
			today.add(Calendar.DAY_OF_YEAR, -100);
			String statement = "delete from ads where date_posted < '"
					+ sdf.format(today.getTime()) + "'";
			stmt.execute(statement);

		} catch (SQLException SQLExcp) {
			System.out.println(SQLExcp);
			Log.info(SQLExcp.getMessage());
		}
	}

	private String dLookUp(java.sql.Statement stat, String table, String fName,
			String where) {
		try {
			ResultSet rs = openrs(stat, "SELECT " + fName + " FROM " + table
					+ " WHERE " + where);
			if (!rs.next()) {
				rs.close();
				return "";
			}
			String res = rs.getString(1);
			rs.close();
			return (res == null ? "" : res);
		} catch (Exception e) {
			return "";
		}
	}

	ResultSet openrs(java.sql.Statement stat, String sql)
			throws java.sql.SQLException {
		ResultSet rs = stat.executeQuery(sql);
		return (rs);
	}

	private StringBuffer getSubCatId(HttpServletRequest req) {
		return null;
	}

	private int getUserId(int advId, Statement stmt) {

		String statement = "select * from ads where ad_id=" + advId
				+ " order by date_posted desc";
		int memId = 0;
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(statement);
			if (rs.next()) {
				memId = rs.getInt("member_id");
			}
		} catch (SQLException SQLExcp) {
			System.out.println(SQLExcp);
			Log.info(SQLExcp.getMessage());
		} finally {
			try {
				rs.close();
			} catch (SQLException SQLExcp) {
				System.out.println(SQLExcp);
				Log.info(SQLExcp.getMessage());
			}
		}
		return memId;
	}
}